<?php
namespace models\repositories;
use Doctrine\ORM\EntityRepository;
use Doctrine\ORM\Query\Expr\Join;
use entities;
class BerkasRepository extends EntityRepository {
	
	public function paginateBerkas($indexAndCount=NULL, $orders=NULL){
		$total = $this->countAll();	
		if ($indexAndCount != NULL){			
			$index = $indexAndCount[0];
			$limit = $indexAndCount[1];
			$offset = ($index - 1 ) * $limit;
			
			if ($offset>0)
				$indexAndCount[0] = $offset;
			
		}		
		
		$qb = $this->_em->createQueryBuilder();
				
		$qb->select('Berkas')
		   ->from('models\entities\Berkas', 'Berkas')
		   ->leftJoin('Berkas.inbox', 'Inbox')
		   ->leftJoin('Berkas.tipe', 'TipeBerkas');
		// $whereClause = "Inbox.procedureStep = 1";   
		// $qb->where($whereClause);  
		if ($orders != NULL && is_array($orders)){
			$qb->orderBy($orders[0]['fieldName'], $orders[0]['orderType']);			
			if (count($orders) > 1){
				for ($i=1;$i<=count($orders);$i++){
					$qb->addOrderBy($orders[$i]['fieldName'], $orders[$i]['typeOrder']);
				} 
			} 
		}   
		   
		if ($indexAndCount != NULL){
			$qb->setFirstResult( $offset )
   		   	   ->setMaxResults( $limit );
		}   
		//die($qb->getQuery()->getSQL());	
		$query = $qb->getQuery(); 
		$result['data'] = $query->getResult();
		$result['count']= $total;
		$result['indexAndCount'] = $indexAndCount;
		return $result;
	}
	
	public function paginateBerkasByMap($map, $indexAndCount=NULL, $orders=NULL){
			
		if ($indexAndCount != NULL){			
			$index = $indexAndCount[0];
			$limit = $indexAndCount[1];
			$offset = ($index - 1 ) * $limit;
			
			if ($offset>0)
				$indexAndCount[0] = $offset;
			
		}		
		
		$qb = $this->_em->createQueryBuilder();
		
		 
		
		$qb->select('Berkas','BerkasTraining','BerkasLkpm', 'BerkasLas')
		   ->from('models\entities\Berkas', 'Berkas')
		   ->leftJoin('Berkas.tipe', 'TipeBerkas')
		   ->innerJoin('Berkas.inbox', 'Inbox')
		   ->innerJoin('Berkas.perpustakaan', 'Perpustakaan')	
		   ->leftJoin('Berkas.berkasTraining', 'BerkasTraining', Join::WITH, $qb->expr()->andX(
        					$qb->expr()->eq('Berkas.id', 'BerkasTraining.id'),
        					$qb->expr()->eq('Berkas.tipe', ':training')
					 ))
		   ->leftJoin('Berkas.berkasLkpm', 'BerkasLkpm', Join::WITH, $qb->expr()->andX(
        					$qb->expr()->eq('Berkas.id', 'BerkasLkpm.id'),
        					$qb->expr()->eq('Berkas.tipe', ':lkpm')
					 ))
		   ->leftJoin('Berkas.berkasLas', 'BerkasLas', Join::WITH, $qb->expr()->andX(
        					$qb->expr()->eq('Berkas.id', 'BerkasLas.id'),
        					$qb->expr()->eq('Berkas.tipe', ':las')
					 ))			 			 
					 ;
		   //->leftJoin('BerkasTraining.jenisTraining', 'JenisTraining');		 
		$whereClause = "";   	
		 
		if (is_array($map['pFieldName'])){
			$i=0;
			foreach ($map['pFieldName'] as $field){
				$dot = explode(".", $field);
				$sub = substr($field, -2);
				if (strtoupper($sub) == "ID" && count($dot)>2){
					$tfield = substr($field,0, strlen($field) - 3);
					$tfield ="IDENTITY({$tfield})";
				} else {
					$tfield = $field;
				}

				$where = "";
				switch ($map['pCondition'][$i]){
					case "LIKE" : $where = "{$tfield} ".$map['pCondition'][$i]."?".$i;
					
					break;
					case "EQUAL" : $where = "{$tfield} = ?".$i;
					
					break; 
					case "NOT_EQUAL" : $where = "{$tfield} != ?".$i;
					break;
					case ">" : $where = "{$tfield} > ?".$i;
					break;
					case "IN" : $where = "{$tfield} in (".$map['pFieldValue'][$i].")";
					//$i++;
					//echo $map['pFieldValue'][$i];
					break;
					case "CUSTOM_SUBQUERY" : $where = "";break;
					break;
				}
				if ($i==0){
					$whereClause = $whereClause . $where;
				} else {
					$whereClause = $whereClause . " and " .$where;	
				}
				if ($map['pCondition'][$i] == "LIKE"){
					$qb->setParameter($i, "%".$map['pFieldValue'][$i]."%");
				} else if ($map['pCondition'][$i] == "IN" || $map['pCondition'][$i] == "NOT_IN") {
				
				} else {
					$qb->setParameter($i, $map['pFieldValue'][$i]);	
				} 				
				$i++;
			}
			$qb->setParameter(':training', 'TRAINING');
			$qb->setParameter(':lkpm', 'LKPM');
			$qb->setParameter(':las', 'LAS');
			$qb->where($whereClause);
			
		}
		// die($whereClause);
		// echo "<pre>";print_r($map);
		// echo $whereClause."--";
		// echo "<pre>";print_r($qb->getQuery()->getSQL());die();		
		$total = $this->countAllByMap($whereClause, $map);
		  
		if ($orders != NULL && is_array($orders)){
			$qb->orderBy($orders[0]['fieldName'], $orders[0]['orderType']);			
			if (count($orders) > 1){
				for ($i=1;$i<=count($orders);$i++){
					$qb->addOrderBy($orders[$i]['fieldName'], $orders[$i]['typeOrder']);
				} 
			} 
		}   
		   
		if ($indexAndCount != NULL){
			$qb->setFirstResult( $offset )
   		   	   ->setMaxResults( $limit );
		}   
			
		$query = $qb->getQuery(); 
		
		$result['data'] = $query->getResult();
		
		$result['count']= $total;
		$result['indexAndCount'] = $indexAndCount;
		return $result;
	}

	public function countAll(){
		$qb = $this->_em->createQueryBuilder();		
		$qb->select('count(a.id)')
		   ->from('models\entities\Berkas', 'a')
		   	   		
		;		
		
		$count = $qb->getQuery()->getSingleScalarResult();
		return $count;
	}
	
	public function countAllByMap($whereClause, $map){
		$qb = $this->_em->createQueryBuilder();		
		$qb->select('count(Berkas.id)')
		   ->from('models\entities\Berkas', 'Berkas')
		   ->leftJoin('Berkas.tipe', 'TipeBerkas')
		   ->innerJoin('Berkas.inbox', 'Inbox')
		   ->innerJoin('Berkas.perpustakaan', 'Perpustakaan');		
		;
		if ($whereClause != ""){
			$qb->where($whereClause);
		}
		$i=0;
		foreach ($map['pFieldValue'] as $p){
			if ($map['pCondition'][$i] == "LIKE"){
				$qb->setParameter($i, "%".$p."%");
				$i++;
			} else if ($map['pCondition'][$i] == "IN") {
				$i++;	
			}else {
				$qb->setParameter($i, $p);
				$i++;	
			}
				
		}
		//echo "<pre>";print_r($whereClause);die();
		$count = $qb->getQuery()->getSingleScalarResult();
		
		return $count;
	}
	
	public function findByOne($isLazzy = FALSE, $criteria){
		if ($isLazzy){
			$qb = $this->getQueryClause();			
			
			$whereClause = $this->getBuildWhere($criteria);
			if ($whereClause != ""){
				$qb->where($whereClause);
			}
			//die($whereClause);
			$i=0;			
			foreach ($criteria as $key => $value){				
				$qb->setParameter($i, $value);
				$i++;
			}
			//die($qb->getQuery()->getSQL());	
			$query = $qb->getQuery(); 
			$berkases = $query->getResult();
			$berkas = NULL;
			if (count($berkases) > 0) {
				$berkas = $berkases[0];
			} 
			
		} else {
			$berkas = parent::findOneBy($criteria);	
		}
		return $berkas;
	}

	public function getQueryClause(){
		$qb = $this->_em->createQueryBuilder();
		
		$qb->select('Berkas')
		   ->from('models\entities\Berkas', 'Berkas')
		   ->leftJoin('Berkas.indicator', 'Indicator');
		   
		return $qb;
	}
	
	public function getBuildWhere($criteria){
		$whereClause = "";   	 
		if (is_array($criteria)){
			$i=0; 
			foreach ($criteria as $field => $value){
				// if (strtoupper($field) == "ID"){
					// $tfield ="Berkas.id";
				// } else {
// 					
				// }
				$tfield ="Berkas.".$field;
				$where = "{$tfield} = ?".$i;
				
				if ($i==0){
					$whereClause = $whereClause . $where;
				} else {
					$whereClause = $whereClause . " and " .$where;	
				}
				
				$i++;
			}
		}
		return $whereClause;
	}
	
	
	public function findNoUrut($tigaKodePertama){
		//$rsm = new \ResultSetMapping();
// build rsm here
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("noUrut", "noUrut");
		$query = $this->_em->createNativeQuery("SELECT max(convert(substr(id, 4, 4), UNSIGNED INTEGER)) noUrut
  														FROM berkas
 													WHERE id LIKE '{$tigaKodePertama}%'",$rsm);
		//$query->setParameter(1, $tigaKodePertama.'%');

		$str = $query->getResult();
		
		//echo "<pre>";print_r($str);die();
		return $str[0]['noUrut'];
	}
		
	public function paginateReportTrainingAbsensi(){
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("nama", "jenisTraining");
		$rsm->addScalarResult("jumlahBerkas", "jumlahBerkas");
		$rsm->addScalarResult("StaffLaki", "staffLaki");
		$rsm->addScalarResult("StaffPr", "staffPr");
		$rsm->addScalarResult("NonStaffLaki", "nonStaffLaki");
		$rsm->addScalarResult("NonStaffPr", "nonStaffPr");
		$rsm->addScalarResult("LainnyaLaki", "lainnyaLaki");
		$rsm->addScalarResult("LainnyaPr", "lainnyaPr");
		$rsm->addScalarResult("Male", "male");
		$rsm->addScalarResult("Female", "female");
		
		// $query = $this->_em->createNativeQuery("select jenis_training.nama, count(berkas.id) jumlahBerkas,
												  	// sum(case when staff = 'S' and gender='M' then 1 else 0 end) StaffLaki,
                          							// sum(case when staff = 'S' and gender='F' then 1 else 0 end) StaffPr,
                          							// sum(case when staff = 'N' and gender='M' then 1 else 0 end) NonStaffLaki,
                          							// sum(case when staff = 'N' and gender='F' then 1 else 0 end) NonStaffPr,
                          							// sum(case when staff = 'L' and gender='M' then 1 else 0 end) LainnyaLaki,
                          							// sum(case when staff = 'L' and gender='F' then 1 else 0 end) LainnyaPr,
												  	// sum(case when gender = 'M' then 1 else 0 end) Male,
												  	// sum(case when gender = 'F' then 1 else 0 end) Female
												// from berkas
												// left join berkas_training on berkas.id = berkas_training.id
												// left join participant_of_training on berkas.id = participant_of_training.berkas_id
												// inner join jenis_training on jenis_training.id = berkas_training.jenis_training
												// where berkas.result = 'FINISH'
												// group by jenis_training.id , jenis_training.nama",$rsm);
		$sqlString = "SELECT jenis_training.id, jenis_training.nama, count(X.id) jumlahBerkas, 
      						SUM(X.StaffLaki) StaffLaki,SUM(X.StaffPr) StaffPr,SUM(X.NonStaffLaki) NonStaffLaki,
      						SUM(X.NonStaffPr) NonStaffPr, SUM(X.LainnyaLaki) LainnyaLaki, SUM(X.LainnyaPr) LainnyaPr,
      						SUM(X.Male) Male, SUM(X.Female) Female     
  						from jenis_training
  							inner join (
              						SELECT berkas.id, berkas_training.jenis_training, sum(CASE WHEN staff = 'S' AND gender = 'M' THEN 1 ELSE 0 END)
                        				StaffLaki,
							                     sum(CASE WHEN staff = 'S' AND gender = 'F' THEN 1 ELSE 0 END) StaffPr,
							                     sum(CASE WHEN staff = 'N' AND gender = 'M' THEN 1 ELSE 0 END)
							                        NonStaffLaki,
							                     sum(CASE WHEN staff = 'N' AND gender = 'F' THEN 1 ELSE 0 END)
							                        NonStaffPr,
							                     sum(CASE WHEN staff = 'L' AND gender = 'M' THEN 1 ELSE 0 END)
							                        LainnyaLaki,
							                     sum(CASE WHEN staff = 'L' AND gender = 'F' THEN 1 ELSE 0 END)
							                        LainnyaPr,
							                     sum(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) Male,
							                     sum(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) Female
							                FROM berkas
							                     INNER JOIN perpustakaan
							                        ON berkas.perpustakaan_id = perpustakaan.id
							                     INNER JOIN desa
							                        ON perpustakaan.desa_id = desa.id
							                     INNER JOIN distrik
							                        ON desa.distrik_id = distrik.id
							                     INNER JOIN propinsi
							                        ON distrik.propinsi_id = propinsi.id
							                     LEFT JOIN berkas_training
							                        ON berkas.id = berkas_training.id
							                     LEFT JOIN participant_of_training
							                        ON berkas.id = participant_of_training.berkas_id
							               WHERE berkas.result = 'FINISH' AND propinsi.id = 11
							              GROUP BY berkas.id
							              ) X on jenis_training.id = X.jenis_training
							              group by jenis_training.id;";												
		$str = $query->getResult();		
		return $str;	
									
	}
	
	public function paginateReportTrainingAbsensiByPropinsiDistrikDesaJenisTraining($par){
		
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("nama", "jenisTraining");
		$rsm->addScalarResult("jumlahBerkas", "jumlahBerkas");
		$rsm->addScalarResult("StaffLaki", "staffLaki");
		$rsm->addScalarResult("StaffPr", "staffPr");
		$rsm->addScalarResult("NonStaffLaki", "nonStaffLaki");
		$rsm->addScalarResult("NonStaffPr", "nonStaffPr");
		$rsm->addScalarResult("LainnyaLaki", "lainnyaLaki");
		$rsm->addScalarResult("LainnyaPr", "lainnyaPr");
		$rsm->addScalarResult("Male", "male");
		$rsm->addScalarResult("Female", "female");
		
		$where = array();
		if ($par['propinsi'] != "-1"){
			$where[] = "propinsi.id=".$par['propinsi'];
		}
		if ($par['distrik'] != "-1"){
			$where[] = "distrik.id=".($par['distrik']);
		}
		if ($par['desa'] != "-1"){
			$where[] = "desa.id=".($par['desa']);
		}
		if ($par['rincian'] != "-1"){
			$where[] = "jenis_training.id='".($par['rincian'])."'";
		}
		if (count($where) > 0){
			$strWhere  = "and ".implode(" and ", $where); 
		} else {
			$strWhere = "";
		}
		
		// $query = $this->_em->createNativeQuery("select jenis_training.nama, count(berkas.id) jumlahBerkas,
												  	// sum(case when staff = 'S' and gender='M' then 1 else 0 end) StaffLaki,
                          							// sum(case when staff = 'S' and gender='F' then 1 else 0 end) StaffPr,
                          							// sum(case when staff = 'N' and gender='M' then 1 else 0 end) NonStaffLaki,
                          							// sum(case when staff = 'N' and gender='F' then 1 else 0 end) NonStaffPr,
                          							// sum(case when staff = 'L' and gender='M' then 1 else 0 end) LainnyaLaki,
                          							// sum(case when staff = 'L' and gender='F' then 1 else 0 end) LainnyaPr,
												  	// sum(case when gender = 'M' then 1 else 0 end) Male,
												  	// sum(case when gender = 'F' then 1 else 0 end) Female
												// from berkas
												// inner join perpustakaan ON berkas.perpustakaan_id = perpustakaan.id
												// inner join desa ON perpustakaan.desa_id = desa.id
                        						// inner join distrik on desa.distrik_id = distrik.id
                        						// inner join propinsi on distrik.propinsi_id = propinsi.id                        
												// left join berkas_training on berkas.id = berkas_training.id
												// left join participant_of_training on berkas.id = participant_of_training.berkas_id
												// inner join jenis_training on jenis_training.id = berkas_training.jenis_training
												// where berkas.result = 'FINISH' ". $strWhere . "
												// group by jenis_training.id , jenis_training.nama",$rsm);
		$sqlString = "SELECT jenis_training.id, jenis_training.nama, count(X.id) jumlahBerkas, 
      						SUM(X.StaffLaki) StaffLaki,SUM(X.StaffPr) StaffPr,SUM(X.NonStaffLaki) NonStaffLaki,
      						SUM(X.NonStaffPr) NonStaffPr, SUM(X.LainnyaLaki) LainnyaLaki, SUM(X.LainnyaPr) LainnyaPr,
      						SUM(X.Male) Male, SUM(X.Female) Female     
  						from jenis_training
  							inner join (
              						SELECT berkas.id, berkas_training.jenis_training, sum(CASE WHEN staff = 'S' AND gender = 'M' THEN 1 ELSE 0 END)
                        				StaffLaki,
							                     sum(CASE WHEN staff = 'S' AND gender = 'F' THEN 1 ELSE 0 END) StaffPr,
							                     sum(CASE WHEN staff = 'N' AND gender = 'M' THEN 1 ELSE 0 END)
							                        NonStaffLaki,
							                     sum(CASE WHEN staff = 'N' AND gender = 'F' THEN 1 ELSE 0 END)
							                        NonStaffPr,
							                     sum(CASE WHEN staff = 'L' AND gender = 'M' THEN 1 ELSE 0 END)
							                        LainnyaLaki,
							                     sum(CASE WHEN staff = 'L' AND gender = 'F' THEN 1 ELSE 0 END)
							                        LainnyaPr,
							                     sum(CASE WHEN gender = 'M' THEN 1 ELSE 0 END) Male,
							                     sum(CASE WHEN gender = 'F' THEN 1 ELSE 0 END) Female
							                FROM berkas
							                     INNER JOIN perpustakaan
							                        ON berkas.perpustakaan_id = perpustakaan.id
							                     INNER JOIN desa
							                        ON perpustakaan.desa_id = desa.id
							                     INNER JOIN distrik
							                        ON desa.distrik_id = distrik.id
							                     INNER JOIN propinsi
							                        ON distrik.propinsi_id = propinsi.id
							                     LEFT JOIN berkas_training
							                        ON berkas.id = berkas_training.id
							                     LEFT JOIN participant_of_training
							                        ON berkas.id = participant_of_training.berkas_id
							               WHERE berkas.result = 'FINISH' and berkas.tipe_berkas='TRAINING' ". $strWhere . "
							              GROUP BY berkas.id
							              ) X on jenis_training.id = X.jenis_training
							              group by jenis_training.id;";		
		$query = $this->_em->createNativeQuery($sqlString, $rsm);							              											
		$str = $query->getResult();	
		// echo $query->getSQL();
		// echo "<pre>"; print_r($str);die();	
		return $str;	
									
	}
	
	public function paginateReportByDistrikDesaTipeBerkas($par){
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("namaDistrik", "namaDistrik");
		$rsm->addScalarResult("namaDesa", "namaDesa");
		$rsm->addScalarResult("tipeBerkas", "tipeBerkas");
		$rsm->addScalarResult("jumlahBerkas", "jumlahBerkas");
		$rsm->addScalarResult("tipeBerkasId", "tipeBerkasId");
		$rsm->addScalarResult("distrikId", "distrikId");
		$rsm->addScalarResult("desaId", "desaId");
		$rsm->addScalarResult("propinsiId", "propinsiId");
		$rsm->addScalarResult("rincianTraining", "rincianTraining");
		$rsm->addScalarResult("rincianLkpm", "rincianLkpm");
		$rsm->addScalarResult("rincianAsistensi", "rincianAsistensi");
		
		$where = array();
		if ($par['distrik'] != "-1"){
			$where[] = "distrik.id=".($par['distrik']);
		}
		if ($par['desa'] != "-1"){
			$where[] = "desa.id=".($par['desa']);
		}
		if ($par['tipeBerkas'] != "-1"){
			$where[] = "tipe_berkas.id='".($par['tipeBerkas'])."'";
		}
		if (count($where) > 0){
			$strWhere  = "and ".implode(" and ", $where); 
		} else {
			$strWhere = "";
		}
		$query = $this->_em->createNativeQuery("SELECT  propinsi.id propinsiId, propinsi.nama namaPropinsi, distrik.nama namaDistrik, desa.nama namaDesa, tipe_berkas.nama tipeBerkas, count(berkas.id) jumlahBerkas, distrik.id distrikId, desa.id desaId, tipe_berkas.id tipeBerkasId
												  , jenis_training.nama rincianTraining, jenis_kegiatan.nama rincianLkpm, CONCAT(CASE WHEN berkas_las.jenis_asistensi = 1 THEN 'NON KUNJUNGAN' WHEN berkas_las.jenis_asistensi = 2 THEN 'KUNJUNGAN' END, ' - ', kategori_asistensi.nama) rincianAsistensi  
												  FROM berkas
												     inner join perpustakaan ON berkas.perpustakaan_id = perpustakaan.id
												     inner join desa ON perpustakaan.desa_id = desa.id
												     inner join distrik on desa.distrik_id = distrik.id
												     inner join propinsi on distrik.propinsi_id = propinsi.id
												     inner join tipe_berkas on berkas.tipe_berkas = tipe_berkas.id	
												     left join berkas_training on berkas.id = berkas_training.id and berkas.tipe_berkas = 'TRAINING'	
												     left join jenis_training on berkas_training.jenis_training = jenis_training.id
												     left join berkas_lkpm on berkas_lkpm.id = berkas.id and berkas.tipe_berkas = 'LKPM'
												     left join jenis_kegiatan on berkas_lkpm.jenis_kegiatan_id = jenis_kegiatan.id
												     left join berkas_las on berkas_las.id = berkas.id and berkas.tipe_berkas = 'LAS'
												     left join kategori_asistensi on kategori_asistensi.id = berkas_las.kategori_asistensi_id										     
												 WHERE berkas.result = 'FINISH' " . $strWhere . "
												 group by berkas.tipe_berkas, distrik.id, desa.id",$rsm);
		$str = $query->getResult();
		
		//echo "<pre>";print_r($str);die();		
		return $str;	
									
	}

	//paginateReportDetilByDistrikDesaTipeBerkas
	public function paginateReportDetilByDistrikDesaTipeBerkas($par){
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("berkasId", "berkasId");
		$rsm->addScalarResult("namaPerpus", "namaPerpus");
		$rsm->addScalarResult("namaTipeBerkas", "namaTipeBerkas");
		$rsm->addScalarResult("mulai", "mulai");
		$rsm->addScalarResult("selesai", "selesai");
		$rsm->addScalarResult("reporter", "reporter");
		$rsm->addScalarResult("btFromDate", "btFromDate");
		$rsm->addScalarResult("btToDate", "btToDate");
		$rsm->addScalarResult("blFromDate", "blFromDate");
		$rsm->addScalarResult("blToDate", "blToDate");
		$rsm->addScalarResult("blkpmFromDate", "blkpmFromDate");
		$rsm->addScalarResult("blkpmToDate", "blkpmToDate");
		$rsm->addScalarResult("tipeBerkasId", "tipeBerkasId");
		
		$where = array();
		if ($par['distrik'] != "-1"){
			$where[] = "distrik.id=".($par['distrik']);
		}
		if ($par['desa'] != "-1"){
			$where[] = "desa.id=".($par['desa']);
		}
		if ($par['tipeBerkas'] != "-1"){
			$where[] = "tipe_berkas.id='".($par['tipeBerkas'])."'";
		}
		if (count($where) > 0){
			$strWhere  = "and ".implode(" and ", $where); 
		} else {
			$strWhere = "";
		}
		$query = $this->_em->createNativeQuery("SELECT  berkas.id berkasId, perpustakaan.nama namaPerpus, tipe_berkas.nama namaTipeBerkas, berkas.mulai, berkas.selesai, berkas.reporter, 
														DATE_FORMAT(bt.from_date,'%d-%m-%Y') btFromDate, DATE_FORMAT(bt.to_date,'%d-%m-%Y') btToDate,
        											    DATE_FORMAT(bl.from_date,'%d-%m-%Y') blFromDate, DATE_FORMAT(bl.to_date,'%d-%m-%Y') blToDate,
        												DATE_FORMAT(blkpm.from_date,'%d-%m-%Y') blkpmFromDate, DATE_FORMAT(blkpm.to_date,'%d-%m-%Y') blkpmToDate,
        												tipe_berkas.id tipeBerkasId
												  FROM berkas
												  	 left join berkas_training bt on bt.id = berkas.id
                             						 left join berkas_las bl on bl.id = berkas.id
                             						 left join berkas_lkpm blkpm on blkpm.id = berkas.id
												     inner join perpustakaan ON berkas.perpustakaan_id = perpustakaan.id
												     inner join desa ON perpustakaan.desa_id = desa.id
												     inner join distrik on desa.distrik_id = distrik.id
												     inner join tipe_berkas on berkas.tipe_berkas = tipe_berkas.id												     
												 WHERE berkas.result = 'FINISH' " . $strWhere . "
												 ",$rsm);
		$str = $query->getResult();
		
		//echo "<pre>";print_r($str);die();		
		return $str;	
									
	}
	
	public function paginateReportDetilByPropinsiDistrikDesaTipeBerkas($par){
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("berkasId", "berkasId");
		$rsm->addScalarResult("namaPerpus", "namaPerpus");
		$rsm->addScalarResult("namaTipeBerkas", "namaTipeBerkas");
		$rsm->addScalarResult("mulai", "mulai");
		$rsm->addScalarResult("selesai", "selesai");
		$rsm->addScalarResult("reporter", "reporter");
		$rsm->addScalarResult("btFromDate", "btFromDate");
		$rsm->addScalarResult("btToDate", "btToDate");
		$rsm->addScalarResult("blFromDate", "blFromDate");
		$rsm->addScalarResult("blToDate", "blToDate");
		$rsm->addScalarResult("blkpmFromDate", "blkpmFromDate");
		$rsm->addScalarResult("blkpmToDate", "blkpmToDate");
		$rsm->addScalarResult("tipeBerkasId", "tipeBerkasId");
		
		$where = array();
		if ($par['propinsi'] != "-1"){
			$where[] = "propinsi.id=".$par['propinsi'];
		}
		if ($par['distrik'] != "-1"){
			$where[] = "distrik.id=".($par['distrik']);
		}
		if ($par['desa'] != "-1"){
			$where[] = "desa.id=".($par['desa']);
		}
		if ($par['tipeBerkas'] != "-1"){
			$where[] = "tipe_berkas.id='".($par['tipeBerkas'])."'";
		}
		if (count($where) > 0){
			$strWhere  = "and ".implode(" and ", $where); 
		} else {
			$strWhere = "";
		}
		$query = $this->_em->createNativeQuery("SELECT  berkas.id berkasId, perpustakaan.nama namaPerpus, tipe_berkas.nama namaTipeBerkas, berkas.mulai, berkas.selesai, berkas.reporter, 
														DATE_FORMAT(bt.from_date,'%d-%m-%Y') btFromDate, DATE_FORMAT(bt.to_date,'%d-%m-%Y') btToDate,
        											    DATE_FORMAT(bl.from_date,'%d-%m-%Y') blFromDate, DATE_FORMAT(bl.to_date,'%d-%m-%Y') blToDate,
        												DATE_FORMAT(blkpm.from_date,'%d-%m-%Y') blkpmFromDate, DATE_FORMAT(blkpm.to_date,'%d-%m-%Y') blkpmToDate,
        												tipe_berkas.id tipeBerkasId
												  FROM berkas
												  	 left join berkas_training bt on bt.id = berkas.id
                             						 left join berkas_las bl on bl.id = berkas.id
                             						 left join berkas_lkpm blkpm on blkpm.id = berkas.id
												     inner join perpustakaan ON berkas.perpustakaan_id = perpustakaan.id
												     inner join desa ON perpustakaan.desa_id = desa.id
												     inner join distrik on desa.distrik_id = distrik.id
												     inner join propinsi on distrik.propinsi_id = propinsi.id
												     inner join tipe_berkas on berkas.tipe_berkas = tipe_berkas.id												     
												 WHERE berkas.result = 'FINISH' " . $strWhere . "
												 ",$rsm);
		$str = $query->getResult();
		
		//echo "<pre>";print_r($str);die();		
		return $str;	
									
	}
	
	public function paginateReportByPropinsiDistrikDesaTipeBerkas($par){
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("namaDistrik", "namaDistrik");
		$rsm->addScalarResult("namaDesa", "namaDesa");
		$rsm->addScalarResult("tipeBerkas", "tipeBerkas");
		$rsm->addScalarResult("jumlahBerkas", "jumlahBerkas");
		$rsm->addScalarResult("tipeBerkasId", "tipeBerkasId");
		$rsm->addScalarResult("distrikId", "distrikId");
		$rsm->addScalarResult("desaId", "desaId");
		$rsm->addScalarResult("rincianTraining", "rincianTraining");
		$rsm->addScalarResult("rincianLkpm", "rincianLkpm");
		$rsm->addScalarResult("rincianAsistensi", "rincianAsistensi");
		
		$where = array();
		if ($par['propinsi'] != "-1"){
			$where[] = "propinsi.id=".$par['propinsi'];
		}
		if ($par['distrik'] != "-1"){
			$where[] = "distrik.id=".($par['distrik']);
		}
		if ($par['desa'] != "-1"){
			$where[] = "desa.id=".($par['desa']);
		}
		if ($par['tipeBerkas'] != "-1"){
			$where[] = "tipe_berkas.id='".($par['tipeBerkas'])."'";
			if ($par['rincian'] != "-1"){
				if ($par['tipeBerkas'] == "TRAINING"){
					$where[] = "jenis_training.id='".($par['rincian'])."'";	
				} else if ($par['tipeBerkas'] == "LKPM"){
					$where[] = "jenis_kegiatan.id=".($par['rincian'])."";
				} else if ($par['tipeBerkas'] == "LAS"){
					$where[] = "kategori_asistensi.id=".($par['rincian'])."";
				} 	
			}
		}
		
		
		if (count($where) > 0){
			$strWhere  = "and ".implode(" and ", $where); 
		} else {
			$strWhere = "";
		}
		//die($strWhere);
		$query = $this->_em->createNativeQuery("SELECT  distrik.nama namaDistrik, desa.nama namaDesa, tipe_berkas.nama tipeBerkas, count(berkas.id) jumlahBerkas, propinsi.id, distrik.id distrikId, desa.id desaId, tipe_berkas.id tipeBerkasId
												  , jenis_training.nama rincianTraining, jenis_kegiatan.nama rincianLkpm, CONCAT(CASE WHEN berkas_las.jenis_asistensi = 1 THEN 'NON KUNJUNGAN' WHEN berkas_las.jenis_asistensi = 2 THEN 'KUNJUNGAN' END, ' - ', kategori_asistensi.nama) rincianAsistensi
												  FROM berkas
												  	 inner join perpustakaan ON berkas.perpustakaan_id = perpustakaan.id
												     inner join desa ON perpustakaan.desa_id = desa.id
												     inner join distrik on desa.distrik_id = distrik.id
												     inner join propinsi on distrik.propinsi_id = propinsi.id
												     inner join tipe_berkas on berkas.tipe_berkas = tipe_berkas.id
												     left join berkas_training on berkas.id = berkas_training.id and berkas.tipe_berkas = 'TRAINING'	
												     left join jenis_training on berkas_training.jenis_training = jenis_training.id
												     left join berkas_lkpm on berkas_lkpm.id = berkas.id and berkas.tipe_berkas = 'LKPM'
												     left join jenis_kegiatan on berkas_lkpm.jenis_kegiatan_id = jenis_kegiatan.id
												     left join berkas_las on berkas_las.id = berkas.id and berkas.tipe_berkas = 'LAS'
												     left join kategori_asistensi on kategori_asistensi.id = berkas_las.kategori_asistensi_id												     
												 WHERE berkas.result = 'FINISH' " . $strWhere . "
												 group by berkas.tipe_berkas, propinsi.id, distrik.id, desa.id",$rsm);
		$str = $query->getResult();
		//echo $query->getSQL();
		//echo "<pre>";print_r($str);die();		
		return $str;	
									
	}

	public function paginateBerkasByMap2($map, $indexAndCount=NULL, $orders=NULL){
			
		if ($indexAndCount != NULL){			
			$index = $indexAndCount[0];
			$limit = $indexAndCount[1];
			$offset = ($index - 1 ) * $limit;
			
			if ($offset>0)
				$indexAndCount[0] = $offset;
			
		}		
		
		$qb = $this->_em->createQueryBuilder();
		
		$qb->select('Berkas')
		   ->from('models\entities\Berkas', 'Berkas')
		   ->leftJoin('Berkas.tipe', 'TipeBerkas')
		   ->leftJoin('Berkas.inbox', 'Inbox')
		   ->leftJoin('Berkas.perpustakaan', 'Perpustakaan')
		   // additional
		   ->innerJoin('Perpustakaan.desa', 'Desa')
		   ->innerJoin('Desa.distrik', 'Distrik')
		   ->innerJoin('Distrik.propinsi', 'Propinsi')
		   ;	
		    
		$whereClause = "";   	 
		if (is_array($map['pFieldName'])){
			$i=0; 
			foreach ($map['pFieldName'] as $field){
				$dot = explode(".", $field);
				$sub = substr($field, -2);
				if (strtoupper($sub) == "ID" && count($dot)>2){
					$tfield = substr($field,0, strlen($field) - 3);
					$tfield ="IDENTITY({$tfield})";
				} else {
					$tfield = $field;
				}

				$where = "";
				switch ($map['pCondition'][$i]){
					case "LIKE" : $where = "{$tfield} ".$map['pCondition'][$i]."?".$i;
					
					break;
					case "EQUAL" : $where = "{$tfield} = ?".$i;
					
					break; 
					case "NOT_EQUAL" : $where = "{$tfield} != ?".$i;
					break;
					case ">" : $where = "{$tfield} > ?".$i;
					break;
					case "IN" : $where = "{$tfield} in (".$map['pFieldValue'][$i].")";
					
					break;
					case "CUSTOM_SUBQUERY" : $where = "";break;
					break;
				}
				if ($i==0){
					$whereClause = $whereClause . $where;
				} else {
					$whereClause = $whereClause . " and " .$where;	
				}
				if ($map['pCondition'][$i] == "LIKE"){
					$qb->setParameter($i, "%".$map['pFieldValue'][$i]."%");
				} else if ($map['pCondition'][$i] == "IN" || $map['pCondition'][$i] == "NOT_IN") {
				
				} else {
					$qb->setParameter($i, $map['pFieldValue'][$i]);	
				} 				
				$i++;
			}
	// print_r($whereClause);die;
			$qb->where($whereClause);
			
		}
		// die($whereClause);
		// echo "<pre>";print_r($map);
		// echo $whereClause."--";
		// echo "<pre>";print_r($qb->getQuery()->getSQL());die();		
		$total = $this->countAllByMap2($whereClause, $map);
		  
		if ($orders != NULL && is_array($orders)){
			$qb->orderBy($orders[0]['fieldName'], $orders[0]['orderType']);			
			if (count($orders) > 1){
				for ($i=1;$i<=count($orders);$i++){
					$qb->addOrderBy($orders[$i]['fieldName'], $orders[$i]['typeOrder']);
				} 
			} 
		}   
		   
		if ($indexAndCount != NULL){
			$qb->setFirstResult( $offset )
   		   	   ->setMaxResults( $limit );
		}   
			
		$query = $qb->getQuery();
		$result['data'] = $query->getResult();
		
		$result['count']= $total;
		$result['indexAndCount'] = $indexAndCount;
		return $result;
	}

	public function countAllByMap2($whereClause, $map){
		$qb = $this->_em->createQueryBuilder();		
		$qb->select('count(Berkas.id)')
		   ->from('models\entities\Berkas', 'Berkas')
		   ->leftJoin('Berkas.tipe', 'TipeBerkas')
		   ->leftJoin('Berkas.inbox', 'Inbox')
		   ->leftJoin('Berkas.perpustakaan', 'Perpustakaan')		
		   // additional
		   ->innerJoin('Perpustakaan.desa', 'Desa')
		   ->innerJoin('Desa.distrik', 'Distrik')
		   ->innerJoin('Distrik.propinsi', 'Propinsi')
		;
		
		if ($whereClause != ""){
			$qb->where($whereClause);
		}
		$i=0;
		foreach ($map['pFieldValue'] as $p){
			if ($map['pCondition'][$i] == "LIKE"){
				$qb->setParameter($i, "%".$p."%");
				$i++;
			} else if ($map['pCondition'][$i] == "IN") {
				$i++;	
			}else {
				$qb->setParameter($i, $p);
				$i++;	
			}
				
		}
		//echo "<pre>";print_r($whereClause);die();
		$count = $qb->getQuery()->getSingleScalarResult();
		
		return $count;
	}
	
	public function paginateSentBerkas($par){
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("namaDistrik", "namaDistrik");
		$rsm->addScalarResult("namaDesa", "namaDesa");
		$rsm->addScalarResult("tipeBerkas", "tipeBerkas");
		$rsm->addScalarResult("jumlahBerkas", "jumlahBerkas");
		$rsm->addScalarResult("tipeBerkasId", "tipeBerkasId");
		$rsm->addScalarResult("distrikId", "distrikId");
		$rsm->addScalarResult("desaId", "desaId");
		
		$where = array();
		if (key_exists($par, "reporter")){
			$where[] = "reporter=".$par['reporter'];
		}
		if (key_exists($par, "reporter")){
			$where[] = "distrik.id=".($par['distrik']);
		}
		if ($par['desa'] != "-1"){
			$where[] = "desa.id=".($par['desa']);
		}
		if ($par['tipeBerkas'] != "-1"){
			$where[] = "tipe_berkas.id='".($par['tipeBerkas'])."'";
		}
		if (count($where) > 0){
			$strWhere  = "and ".implode(" and ", $where); 
		} else {
			$strWhere = "";
		}
		$query = $this->_em->createNativeQuery("select * from berkas 
												left join inbox on berkas.id = inbox.id ".$strWhere,$rsm);
		$str = $query->getResult();
		//echo $query->getSQL();
		//echo "<pre>";print_r($str);die();		
		return $str;	
									
	}

	public function findPerpustakaanProcedureStepProfilReviewer(){
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("perpustakaan_id", "perpustakaanId");
		$rsm->addScalarResult("profile", "profile");
		$rsm->addScalarResult("procedure_step", "procedureStep");
		
		$query = $this->_em->createNativeQuery("
											SELECT berkas.perpustakaan_id, profile, procedure_step
											  FROM berkas 
											  INNER JOIN inbox on berkas.id = inbox.id
											  inner join procedure_step on procedure_step.id = inbox.procedure_step
											  where inbox.procedure_step in (2,3,4,6,9); ", $rsm);
		$str = $query->getResult();
		//echo $query->getSQL();
		//echo "<pre>";print_r($str);die();		
		return $str;											 
	}	

	
	public function findNumberFinalBerkas(){
		
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("jumlah", "jumlah");
		$query = $this->_em->createNativeQuery("SELECT count(*) jumlah
  														FROM berkas
 													WHERE result = 'FINISH'",$rsm);
		
		$str = $query->getResult();
		
		return $str[0]['jumlah'];
	}
	
	public function findNumberCFBerkas(){
		
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("jumlah", "jumlah");
		$query = $this->_em->createNativeQuery("SELECT count(*) jumlah
  														FROM berkas
  														inner join inbox on inbox.id = berkas.id
 													WHERE inbox.procedure_step = 1",$rsm);
		
		$str = $query->getResult();
		
		return $str[0]['jumlah'];
	}
	
	public function findNumberCFBerkasByUser($userId){
		
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("jumlah", "jumlah");
		$query = $this->_em->createNativeQuery("SELECT count(*) jumlah
  														FROM berkas
  														inner join inbox on inbox.id = berkas.id
 													WHERE inbox.procedure_step = 1 and berkas.reporter = :userId ",$rsm);
		$query->setParameter('userId', $userId);
		$str = $query->getResult();
		
		return $str[0]['jumlah'];
	}
	
	public function findNumberCFBerkasProgressByUser($userId){
		
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("jumlah", "jumlah");
		$query = $this->_em->createNativeQuery("SELECT count(*) jumlah
  														FROM berkas
  														inner join inbox on inbox.id = berkas.id
 													WHERE inbox.procedure_step in (2,3,9) and berkas.reporter = :userId ",$rsm);
		$query->setParameter('userId', $userId);
		$str = $query->getResult();
		
		return $str[0]['jumlah'];
	}
	
	public function findNumberFinalBerkasByUser($userId){
		
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("jumlah", "jumlah");
		$query = $this->_em->createNativeQuery("SELECT count(*) jumlah
  														FROM berkas
 													WHERE result = 'FINISH' AND berkas.reporter=:userId",$rsm);
		$query->setParameter('userId', $userId);
		$str = $query->getResult();
		
		return $str[0]['jumlah'];
	}
	
	public function findNumberDQABerkas(){
		
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("jumlah", "jumlah");
		$query = $this->_em->createNativeQuery("SELECT count(*) jumlah
  														FROM berkas
  														inner join inbox on inbox.id = berkas.id
 													WHERE inbox.procedure_step = 2",$rsm);
		
		$str = $query->getResult();
		
		return $str[0]['jumlah'];
	}
	
	public function findNumberCBOBerkas(){
		
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("jumlah", "jumlah");
		$query = $this->_em->createNativeQuery("SELECT count(*) jumlah
  														FROM berkas
  														inner join inbox on inbox.id = berkas.id
 													WHERE inbox.procedure_step = 3",$rsm);
		
		$str = $query->getResult();
		
		return $str[0]['jumlah'];
	}
	
	public function findNumberMEBerkas(){
		
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("jumlah", "jumlah");
		$query = $this->_em->createNativeQuery("SELECT count(*) jumlah
  														FROM berkas
  														inner join inbox on inbox.id = berkas.id
 													WHERE inbox.procedure_step = 9",$rsm);
		
		$str = $query->getResult();
		
		return $str[0]['jumlah'];
	}
	
	public function findNumberBerkasByType($status){
		
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("jumlah", "jumlah");
		$rsm->addScalarResult("nama", "nama");
		$query = $this->_em->createNativeQuery("SELECT b.`nama`,COUNT(*) jumlah FROM berkas a
LEFT JOIN tipe_berkas b ON a.`tipe_berkas` = b.`id` 
LEFT JOIN inbox c ON a.`id` = c.`id` 
INNER JOIN perpustakaan d ON d.`id` = a.`perpustakaan_id` 
WHERE c.`procedure_step` = 1 AND c.`status` = '".$status."' 
GROUP BY a.`tipe_berkas`",$rsm);
		
		return $query->getResult();
	}

	
	public function findToExportXls(){
		$rsm = new \Doctrine\ORM\Query\ResultSetMapping;
		$rsm->addScalarResult("berkas_id", "berkasId");
		$rsm->addScalarResult("perpustakaan_id", "perpustakaanId");
		$rsm->addScalarResult("description", "berkasDescription");
		$rsm->addScalarResult("result", "berkasResult");
		$rsm->addScalarResult("mulai", "berkasMulai");
		$rsm->addScalarResult("selesai", "berkasSelesai");
		$rsm->addScalarResult("reporter", "berkasReporter");
		$rsm->addScalarResult("update_date", "berkasUpdateDate");
		$rsm->addScalarResult("verifier", "berkasVerifier");
		$rsm->addScalarResult("approver", "berkasApprover");
		$rsm->addScalarResult("tiper_berkas", "berkasTipe");
		$rsm->addScalarResult("hasil_kegiatan", "berkasHasilKergiatan");
		$rsm->addScalarResult("tantangan", "berkasTantangan");
		$rsm->addScalarResult("nama", "perpustakaanName");
		$rsm->addScalarResult("name", "procedureName");
		$rsm->addScalarResult("type", "procedureType");
		$rsm->addScalarResult("profile", "procedureProfile");
		
		$query = $this->_em->createNativeQuery("
			SELECT a.id as berkas_id,a.*,b.*,c.*,d.* FROM berkas a
				INNER JOIN inbox b on a.id = b.id
				INNER JOIN perpustakaan c on a.perpustakaan_id = c.id
				INNER JOIN procedure_step d on d.id = b.procedure_step; ", $rsm);
				
		$str = $query->getResult();
		return $str;
	}

}

